import { ALL, Body, Inject, Post, Provide } from '@midwayjs/decorator';
import { CoolController, BaseController, BaseService } from 'midwayjs-cool-core';
import { Context } from 'vm';
import { CourseTypeEntity } from '../../../courseType/entity/courseType';
import { courseSystemEntity } from '../../entity/courseSystem';
import { courseSystemService } from '../../service/courseSystem';

/**
 * 描述
 */
@Provide()
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: courseSystemEntity,
  service: courseSystemService,
  pageQueryOp: {
    // 关联表用户表
    // leftJoin: [{
    //   entity: courseSourceEntity,
    //   alias: 'b',
    //   condition: 'a.course_type = b.course_type'
    // }],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    },
    // 添加排序
    addOrderBy: {
      sort: 'desc'
    }
  },
  listQueryOp: {
    select: ['a.*', 'b.course_id'],
    keyWordLikeFields: ['phase_name'],
    // 让type字段支持筛选
    fieldEq: ['course_type'],
    leftJoin: [{
      entity: CourseTypeEntity,
      alias: 'b',
      condition: 'a.course_type = b.id'
    }],
    where: async (ctx: Context) => {
      return [
        ['a.isDelete = :isDelete', { isDelete: false }]
      ]
    },
    // 添加排序
    addOrderBy: {
      sort: 'asc'
    }
  }
})
export class courseSystemAdminController extends BaseController {
  
  @Inject()
  service:BaseService
  /**
  * 自定义接口，通过课程类型获取该课程类型下所有的课程体系，然后返回
  * @param query
  */
  @Post('/courseList')
  async courseList(@Body(ALL) queryObject: any) {
    console.log(queryObject,11111111);
    
    const info = await this.service.nativeQuery(
      `
      select concat(a.sort,'-',a.phase_name) as phase_name,a.phase_course_introduce,group_concat(b.phase_sort,b.section_theme,":,",COALESCE(b.studentName, '0无') separator ';') as studentName from course_system a
      left join (
        select a.id,a.phase_sort,a.phase_name,a.section_theme,group_concat(b.studentName) as studentName from course_source a
        left join (
          select a.section_theme,group_concat(b.studentName) as studentName from class_teaching a
          left join (
            select a.class_teaching,group_concat(a.isPlacement,b.name) as studentName,group_concat(b.id) as class_students from student_class_detail a 
            left join student b on a.student = b.id
            where a.isLeave = false and a.isDelete = false and b.isDelete = false
            group by a.class_teaching
          ) b on a.id = b.class_teaching
          where a.teaching_class = ${queryObject.classgroup_id} and a.isDelete = false
          group by a.section_theme
        ) b on a.id = b.section_theme
        where 1=1 and a.isDelete = false and a.isUse = true
        group by a.id
        order by a.phase_sort asc
      ) b on a.id = b.phase_name
      where a.course_type = ${queryObject.course_type} and a.isDelete = false
      group by a.id
      order by a.sort asc
      `
    )
    
    return {code:1000,data:info}
  }

  @Post('/classCourseStudents')
  async classCourseStudents(@Body(ALL) queryObject: any) {
    console.log(queryObject,11111111);
    

    const info = await this.service.nativeQuery(
      `

      

      (
        select a.id,a.isUse,a.section_theme,concat(b.course_id,'-',b.course_name) as course_type_name,"无" as studentName,c.phase_name from course_source a 
        left join course_type b on a.course_type = b.id
        left join course_system c on c.id = a.phase_name
        where a.course_type  = ${queryObject.course_type} and  a.id not in (
          select section_theme from class_teaching where id in (
            select class_teaching from student_class_detail 
            where isDelete = false and isLeave = false and student in (
              select student_id from classgroup_students where isDelete = false and classgroup_id in (${queryObject.classgroup_id})
            )
          )
        )
      )
      union all
      (
        select c.id,c.isUse,c.section_theme,concat(d.course_id,'-',d.course_name) as course_type_name,ifnull(group_concat(b.studentName),"无") as studentName,e.phase_name from class_teaching a 
        left join (
          select a.class_teaching,group_concat(b.name) as studentName from student_class_detail a
          left join student b on a.student = b.id
          where a.isDelete = false and a.isLeave = false and a.student in (
            select student_id from classgroup_students where isDelete = false and classgroup_id in (${queryObject.classgroup_id})
          )
          group by a.class_teaching
        ) b on a.id = b.class_teaching
        left join course_source c on a.section_theme = c.id
        left join course_type d on c.course_type = d.id
        left join course_system e on e.id = c.phase_name
        where c.course_type = ${queryObject.course_type} and a.id in (
          select class_teaching from student_class_detail 
          where isDelete = false and isLeave = false and student in (
            select student_id from classgroup_students where isDelete = false and classgroup_id in (${queryObject.classgroup_id})
          )
        )
        group by a.section_theme
      )


      `
    )
    
    return {code:1000,data:info}
  }



  @Post('/manyClassCourseStudents')
  async manyClassCourseStudents(@Body(ALL) queryObject: any) {
    
    const { classgroupsId,courseType,courseTheme } = queryObject
    
    const info = await this.service.nativeQuery(
      `

      

      (
        select a.id,a.isUse,a.section_theme,concat(b.course_id,'-',b.course_name) as course_type_name,"无" as studentName,c.phase_name from course_source a 
        left join course_type b on a.course_type = b.id
        left join course_system c on c.id = a.phase_name
        where 
        a.course_type = ${courseType}
        ${this.service.setSql(courseTheme, 'and (a.section_theme LIKE ? )', [
          `%${courseTheme}%`,
        ])} 
         and  a.id not in (
          select section_theme from class_teaching where id in (
            select class_teaching from student_class_detail 
            where isDelete = false and isLeave = false and student in (
              select student_id from classgroup_students where isDelete = false and classgroup_id in (${classgroupsId})
            )
          )
        )
        order by a.updateTime desc
      )
      union all
      (
        select c.id,c.isUse,c.section_theme,concat(d.course_id,'-',d.course_name) as course_type_name,ifnull(group_concat(b.studentName),"无") as studentName,e.phase_name from class_teaching a 
        left join (
          select a.class_teaching,group_concat(b.name) as studentName from student_class_detail a
          left join student b on a.student = b.id
          where a.isDelete = false and a.isLeave = false and a.student in (
            select student_id from classgroup_students where isDelete = false and classgroup_id in (${classgroupsId})
          )
          group by a.class_teaching
        ) b on a.id = b.class_teaching
        left join course_source c on a.section_theme = c.id
        left join course_type d on c.course_type = d.id
        left join course_system e on e.id = c.phase_name
        where 
        c.course_type = ${courseType}
        ${this.service.setSql(courseTheme, 'and (c.section_theme LIKE ? )', [
          `%${courseTheme}%`,
        ])} 
        and a.id in (
          select class_teaching from student_class_detail 
          where isDelete = false and isLeave = false and student in (
            select student_id from classgroup_students where isDelete = false and classgroup_id in (${classgroupsId})
          )
        )
        group by a.section_theme
      )      
      
      `  
    )
    
    
    // 获取所有班级名称
    const className = await this.service.nativeQuery(
      `
      select concat(class_date,class_name,class_number) as className from classgroup where id in (${classgroupsId})
      `  
    )

    // 获取所有班级学生
    const classStudents = await this.service.nativeQuery(
      `
      select b.name as classStudents from classgroup_students a
      left join student b on a.student_id = b.id
      where classgroup_id in (${classgroupsId})
      `  
    )
   
   
    
    return {code:1000,data:{info:info,className:className,classStudents:classStudents}}
  }
}
